SelectLocal

SelectLocal([sourcedatasource, ]selectstring, datasource)

Copy a set of records from one local database table to another. The tables may be in different local databases. The records to copy are specified using a SQL Select statement.

All

sourcedatasource - optional

If both the source and destination tables are held in the same local database, i.e. they have the same value in the Device Database Property, do not include this parameter.

If the source and destination tables are held in different local databases, specify the source Data Source here – click here to see how to specify Data Source(s) in this parameter.

The source can be a Custom View.

 

selectstring - required

String containing a valid SQL Select request to be passed to the local SQL server on the device, or an empty string, "", if the SQL Select statement will be supplied using the SetSelectString Scripting Method instead.

If you specify the SQL Select statement here, you must enter a full Select statement e.g. "SELECT * FROM table1 WHERE user = 'John'". The SELECT statement can start with SELECT or SELECT DISTINCT and columns can be referred to using the format <table>.<column> but <table>.* is not supported. You may want to specify the order in which records should be returned by including an ORDER BY clause, e.g. "SELECT * FROM table1 WHERE user = 'John' ORDER BY table1.userID".

The SQL statement will be run on the local device and hence must be SQLite compatible.

If you want to use this Method with a Custom View, you must specify the SQL Select statement here, you cannot use SetSelectString with Custom Views.

See Notes below for further information.

 

datasource - required

Specifies the destination Data Source – click here for the format of this parameter.

nothing

• Tables used within the Select string are the actual table names in the local database and not the Data Source names.

• When specifying your select string, care must be taken in understanding how columns in the selectstring are mapped to destination columns in datasource. The following algorithm is used to calculate how the fields defined in selectstring are to be mapped to those in datasource:

  For selectstring similar to "SELECT * FROM table1":

  • datasource must contain the same fields or be a subset of table1.

  • It uses all the column names specified in datasource.

  For selectstring similar to "SELECT col1,col2 ... FROM table1":

  • datasource can contain anything but must have the same number of fields as columns in the select statement.

  • The name and order of the fields is important. The column name will be searched for in datasource and will be used if found, otherwise it will use the field at that offset.

    E.g. "SELECT s1,s2 FROM table1" where table1 contains fields s1 and s2. If datasource contains fields d1,d2 it will select s1 into d1 and s2 into d2. However, if datasource contains fields s2,s1 it will select s1 into s1 and s2 into s2.

  • Any columns in the destination datasource which aren't included within the copy will be filled with the default value for that column if a default value is specified in the Data Source definition.

   

• This Method will take longer to return if the optional first parameter, sourcedata, is included.

• You cannot use this Method if the destination Data Source is locked because of an ongoing asynchronous data transfer. Use the IsDataSourceLocked Method to check whether the table is locked before making a call to SelectLocal.

• You can either specify the SQL Select statement, identifying the records to be copied, within the parameters or supply a SQL Select statement using the SetSelectString Scripting Method, which has the advantage of allowing you to supply data values as parameters to the Select statement.

• This Method is affected by the value of the Enforce NOT NULL Property available in the Project Properties.

  This Property determines whether Null values can be entered into columns in a locally held table with out checking whether the associated remote database allows a Null value in its equivalent column or not.

  If you are upgrading from a version of Digitise Apps' predecessor MX, before MX v8, the Client ignored whether a remote column was allowed to contain Null values when updating local records. This meant that you could enter a Null value in a locally held table which would then throw an error when the data was synchronised with the remote database if a column contained a Null when it shouldn't. From MX v8 onwards, by default, this behaviour is prevented, which means that you must supply a valid value to be entered into columns which are not nullable when updating locally held records. In order to provide backwards compatibility for apps created prior to MX v8, the Enforce NOT NULL Property was added to the Project Properties to allow you to retain the original behaviour and thus prevent you from having to change any earlier apps.

  By default, the Property is set to True, which means that at runtime you will not be able to update a column with a Null value if the remote column is set to not allow Nulls. If set to False, the Client will revert to the previous behaviour and allow Nulls to be entered into such columns.

  When a new record is created in a local table all columns are set to Null. If Enforce NOT NULL is True when the record is updated the values will be checked and an error thrown if a column which doesn't allow Nulls is not updated with a valid value.